from sqlalchemy.future import select
from sqlalchemy.ext.asyncio import AsyncSession
from typing import List, Optional

from app.models.student import Student
from app.schemas.student import StudentCreate, StudentUpdate

async def get_student(db: AsyncSession, student_id: int) -> Optional[Student]:
    result = await db.execute(select(Student).filter(Student.id == student_id))
    return result.scalars().first()

async def get_student_by_email(db: AsyncSession, email: str) -> Optional[Student]:
    result = await db.execute(select(Student).filter(Student.email == email))
    return result.scalars().first()

async def get_students(db: AsyncSession, skip: int = 0, limit: int = 100) -> List[Student]:
    result = await db.execute(select(Student).offset(skip).limit(limit))
    return result.scalars().all()

async def create_student(db: AsyncSession, student: StudentCreate) -> Student:
    db_student = Student(** student.dict())
    db.add(db_student)
    await db.commit()
    await db.refresh(db_student)
    return db_student

async def update_student(
    db: AsyncSession, 
    student_id: int, 
    student_update: StudentUpdate
) -> Optional[Student]:
    result = await db.execute(select(Student).filter(Student.id == student_id))
    db_student = result.scalars().first()
    
    if db_student:
        update_data = student_update.dict(exclude_unset=True)
        for key, value in update_data.items():
            setattr(db_student, key, value)
        await db.commit()
        await db.refresh(db_student)
    return db_student

async def delete_student(db: AsyncSession, student_id: int) -> Optional[Student]:
    result = await db.execute(select(Student).filter(Student.id == student_id))
    db_student = result.scalars().first()
    
    if db_student:
        await db.delete(db_student)
        await db.commit()
    return db_student